﻿-- =============================================
-- Author:		<Author by 徐福龙>
-- Create date: <Create Date,2012 02 17>
-- Description:	<Description,用于查看员工自己的工资记录>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Wage_Getlist_Search_Item]
	(
		@CompanyId int,
		@Month nvarchar(7),
		@UserName nvarchar(50)
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering ;WITH SELECT statements.
	SET NOCOUNT ON;

	;WITH list As(Select 
		ROW_NUMBER() OVER (ORDER BY id) AS Row
		,Id
		,GId
		,UserName
		,EName
		,CompanyId
		,(Select CompanyName From Company Where Id=Employee_Wage.CompanyId) As CompanyName
		,DepartmentId
		,DepName
		,ZhiWu
		,GradeId
		,GradeName
		,PostCategoryId
		,PostCategoryName
		,PostId
		,PostName
		,ChuQinDays
		,BasicWage
		,PostWage
		,WorkWage
		,JxWage
		,ManQin
		,Allowance
		--新加的字段 开始
		,[XSTC]
		,[XFTC]
		,[BLTC]
		,[LSwance]
		,[ZBwance]
		,[GJJKouKuan]
		,[GRSDSKouKuan]
		--统计结果
		--,(BasicWage+PostWage+WorkWage+JxWage+Allowance
		--+XSTC+XFTC+BLTC+MTiCheng
		--+FixedAllowance+LSwance+ZBwance
		--+ManQin+YjBonus
		---ChuQinKouKuan-BxKouKuan-GJJKouKuan-ChiDaoKouKuan-OtherKouKuan-GRSDSKouKuan
		--) AS YFHeJi
		,YFHeJi
		--新加的字段 结束
		,FixedAllowance as FAllowance  
		---- 程序中使用的均为 FAllowance
		,QtAllowance
		,TiCheng
		,MTiCheng
		,YjBonus
		,LsBonus
		,ChuQinKouKuan
		,BxKouKuan
		,ChiDaoKouKuan
		,YJKouKuan
		,OtherKouKuan
		,HeJiWage
		,FaFangMonth
		,CreateName
		,CreateDate
		,HRFlag
		,ISNULL(HRFlagDate,'1900-01-01') As HRFlagDate
		,FinanceFlag
		,ISNULL(FinanceFlagDate,'1900-01-01') As FinanceFlagDate
		, isnull(Deduction,0) as Deduction,
		  isnull(bxje,0) as bxje,
		    isnull(msje,0) as msje
		From Employee_Wage 
		Where /*(CompanyId=@CompanyId) and*/ UserName=@UserName and HRFlag=2 and FinanceFlag=2 and Convert(nchar(4),FaFangMonth,120)=@Month
	)
	Select *,
		convert(varchar(10),isnull((select ruzhidate from Employee where list.UserName=employee.username ) ,''),120) as ruzhidate
 From list order by FaFangMonth

END
